package com.myMinistry.provider;

import java.io.File;
import java.io.IOException;
import java.util.Calendar;
import java.util.Locale;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.os.Environment;
import android.provider.BaseColumns;

import com.myMinistry.Helper;
import com.myMinistry.provider.MinistryContract.EntryType;
import com.myMinistry.provider.MinistryContract.Householder;
import com.myMinistry.provider.MinistryContract.Joins;
import com.myMinistry.provider.MinistryContract.LeftJoins;
import com.myMinistry.provider.MinistryContract.Literature;
import com.myMinistry.provider.MinistryContract.LiteraturePlaced;
import com.myMinistry.provider.MinistryContract.LiteratureType;
import com.myMinistry.provider.MinistryContract.Notes;
import com.myMinistry.provider.MinistryContract.PioneeringType;
import com.myMinistry.provider.MinistryContract.Publisher;
import com.myMinistry.provider.MinistryContract.Qualified;
import com.myMinistry.provider.MinistryContract.Rollover;
import com.myMinistry.provider.MinistryContract.Time;
import com.myMinistry.provider.MinistryContract.TimeHouseholder;
import com.myMinistry.provider.MinistryContract.UnionsNameAsCols;
import com.myMinistry.provider.MinistryDatabase.Tables;
import com.myMinistry.util.FileUtils;
import com.myMinistry.util.TimeUtils;

public class MinistryService {
	public static final int ACTIVE = 1;
	public static final int INACTIVE = 0;
	private final Context context;
    private static SQLiteDatabase sqlDB;
    
    public MinistryService(Context context) {
    	this.context = context;
    }

	public MinistryService openWritable() throws SQLException {
		sqlDB = MinistryDatabase.getInstance(context).getWritableDatabase();
    	return this;
    }
    
    public boolean isOpen() {
    	return sqlDB.isOpen();
    }
    
    public void close() {
    	if(sqlDB != null && isOpen())
    		sqlDB.close();
    }
    
	public Cursor fetchActivePublishers() {
		return sqlDB.query(Tables.PUBLISHERS, new String[] {Publisher._ID, Publisher.NAME, Publisher.ACTIVE }, Publisher.ACTIVE + "=" + ACTIVE, null, null, null, Publisher.DEFAULT_SORT, null);
    }
    
	public Cursor fetchAllPublishers(SQLiteDatabase db) {
		return db.query(Tables.PUBLISHERS, new String[] {Publisher._ID, Publisher.NAME, Publisher.ACTIVE }, null, null, null, null, Publisher.DEFAULT_SORT, null);
    }
    
	public Cursor fetchAllPublishers() {
		return sqlDB.query(Tables.PUBLISHERS, new String[] {Publisher._ID, Publisher.NAME, Publisher.ACTIVE }, null, null, null, null, Publisher.DEFAULT_SORT, null);
    }
    
	public Cursor fetchAllPublishersWithActivityDates() {
		String sql =	"SELECT " + Publisher._ID + "," + Publisher.NAME + UnionsNameAsCols.TITLE + "," + Publisher.ACTIVE + UnionsNameAsCols.ACTIVE
				+ ", (SELECT " + Qualified.TIME_DATE_START + " FROM " + Tables.TIMES + " WHERE " + Qualified.TIME_PUBLISHER_ID + " = " + Qualified.PUBLISHER_ID + " ORDER BY " + Qualified.TIME_DATE_START + " DESC LIMIT 1)" + UnionsNameAsCols.DATE
				+ "," + MinistryDatabase.ID_UNION_TYPE_PERSON + UnionsNameAsCols.TYPE_ID
				+ " FROM " + Tables.PUBLISHERS
				+ " ORDER BY " + Publisher.DEFAULT_SORT;
		
		return sqlDB.rawQuery(sql, null);
    }
	
	public int fetchBooksPlacedCountForPublisher(String formattedDate, String timeFrame, int publisherID) {
		int retVal = 0;
    	String sql	=	"SELECT SUM(" + Qualified.PLACED_LITERATURE_COUNT + " * " + Qualified.LITERATURE_WEIGHT + ") AS " + LiteraturePlaced.COUNT
    					+ " FROM " + Tables.PLACED_LITERATURE
    					+ Joins.LITERATURE_JOIN_PLACED_LITERATURE
    					+ Joins.TYPE_LITERATURE_JOIN_LITERATURE
    					+ " WHERE " + Qualified.TYPE_OF_LITERATURE_ID + " = 1"
    					+ " AND " + Qualified.PLACED_LITERATURE_PUBLISHER_ID + " = " + publisherID
    					+ " AND date(" + Qualified.PLACED_LITERATURE_DATE + ") >= date('" + formattedDate + "','start of month')"
    					+ " AND date(" + Qualified.PLACED_LITERATURE_DATE + ") < date('" + formattedDate + "','start of month','+1 " + timeFrame + "')";
    	
    	Cursor record = sqlDB.rawQuery(sql, null);
    	if(record.moveToFirst())
			retVal = record.getInt(0); 
    	if(record != null && !record.isClosed())
    		record.close();
		return retVal;
	}
	
	public int fetchBrochuresPlacedCountForPublisher(String formattedDate, String timeFrame, int publisherID) {
		int retVal = 0;
    	String sql	=	"SELECT SUM(" + Qualified.PLACED_LITERATURE_COUNT + " * " + Qualified.LITERATURE_WEIGHT + ") AS " + LiteraturePlaced.COUNT
    					+ " FROM " + Tables.PLACED_LITERATURE
    					+ Joins.LITERATURE_JOIN_PLACED_LITERATURE
    					+ Joins.TYPE_LITERATURE_JOIN_LITERATURE
    					+ " WHERE " + Qualified.TYPE_OF_LITERATURE_ID + " = 2"
    					+ " AND " + Qualified.PLACED_LITERATURE_PUBLISHER_ID + " = " + publisherID
    					+ " AND date(" + Qualified.PLACED_LITERATURE_DATE + ") >= date('" + formattedDate + "','start of month')"
    					+ " AND date(" + Qualified.PLACED_LITERATURE_DATE + ") < date('" + formattedDate + "','start of month','+1 " + timeFrame + "')";
    	
    	Cursor record = sqlDB.rawQuery(sql, null);
    	if(record.moveToFirst())
			retVal = record.getInt(0); 
    	if(record != null && !record.isClosed())
    		record.close();
		return retVal;
	}
	
	public int fetchMagazinesPlacedCountForPublisher(String formattedDate, String timeFrame, int publisherID) {
		int retVal = 0;
    	String sql	=	"SELECT SUM(" + Qualified.PLACED_LITERATURE_COUNT + " * " + Qualified.LITERATURE_WEIGHT + ") AS " + LiteraturePlaced.COUNT
    					+ " FROM " + Tables.PLACED_LITERATURE
    					+ Joins.LITERATURE_JOIN_PLACED_LITERATURE
    					+ Joins.TYPE_LITERATURE_JOIN_LITERATURE
    					+ " WHERE " + Qualified.TYPE_OF_LITERATURE_ID + " = 3"
    					+ " AND " + Qualified.PLACED_LITERATURE_PUBLISHER_ID + " = " + publisherID
    					+ " AND date(" + Qualified.PLACED_LITERATURE_DATE + ") >= date('" + formattedDate + "','start of month')"
    					+ " AND date(" + Qualified.PLACED_LITERATURE_DATE + ") < date('" + formattedDate + "','start of month','+1 " + timeFrame + "')";
    	
    	Cursor record = sqlDB.rawQuery(sql, null);
    	if(record.moveToFirst())
			retVal = record.getInt(0); 
    	if(record != null && !record.isClosed())
    		record.close();
		return retVal;
	}
	
	public int fetchReturnVisitCountForPublisher(String formattedDate, String timeFrame, int publisherID) {
		int retVal = 0;
		String sql = 	"SELECT COUNT(" + Qualified.TIMEHOUSEHOLDER_ID + ")"
						+ " FROM " + Tables.TIME_HOUSEHOLDERS
						+ Joins.TIME_JOIN_TIMEHOUSEHOLDER
						+ " WHERE " + Time.PUBLISHER_ID + " = " + publisherID
						+ " AND date(" + Time.DATE_START + ") >= date('" + formattedDate + "','start of month')"
    					+ " AND date(" + Time.DATE_START + ") < date('" + formattedDate + "','start of month','+1 " + timeFrame + "')"
    					+ " AND " + Qualified.TIMEHOUSEHOLDER_IS_RETURN_VISIT + " = 1"
    					+ " AND " + Qualified.TIMEHOUSEHOLDER_HOUSEHOLDER_ID + " <> " + MinistryDatabase.NO_HOUSEHOLDER_ID;
		
		Cursor record = sqlDB.rawQuery(sql, null);
    	
    	if(record.moveToFirst())
			retVal = record.getInt(0);
    	
    	if(record != null && !record.isClosed())
    		record.close();
    	return retVal;
	}
	
	public int fetchStudyCountForPublisher(String formattedDate, String timeFrame, int publisherID) {
		int retVal = 0;
		String sql	= 	"SELECT " + Qualified.TIMEHOUSEHOLDER_ID
						+ " , strftime('%m', date(startDate)) as month"
						+ " FROM " + Tables.TIME_HOUSEHOLDERS
						+ Joins.TIME_JOIN_TIMEHOUSEHOLDER
						+ " WHERE " + Time.PUBLISHER_ID + " = " + publisherID
						+ " AND " + TimeHouseholder.STUDY + " = " + ACTIVE
						+ " AND date(" + Time.DATE_START + ") >= date('" + formattedDate + "','start of month')"
						+ " AND date(" + Time.DATE_START + ") < date('" + formattedDate + "','start of month','+1 " + timeFrame + "')"
		    			+ " AND " + Qualified.TIMEHOUSEHOLDER_HOUSEHOLDER_ID + " <> 0"
				    	+ " AND " + Qualified.TIMEHOUSEHOLDER_IS_RETURN_VISIT + " = 1"
						+ " GROUP BY month, " + TimeHouseholder.HOUSEHOLDER_ID;
		
		Cursor record = sqlDB.rawQuery(sql, null);
		retVal = record.getCount();
		if(record != null && !record.isClosed())
			record.close();
		return retVal;
	}
	
	public Cursor fetchListOfHoursForPublisher(String formattedDate, int publisherID, String timeFrame) {
		String sql1 =	"SELECT " + Time.DATE_START + "||\" \"||" + Time.TIME_START + UnionsNameAsCols.DATE_START + "," + Time.DATE_END + "||\" \"||" + Time.TIME_END + UnionsNameAsCols.DATE_END + "," + Qualified.TIME_ID
    					+ " FROM " + Tables.TIMES
    					+ " INNER JOIN " + Tables.ENTRY_TYPES + " ON " + Qualified.ENTRY_TYPE_ID + " = " + Qualified.TIME_ENTRY_TYPE_ID
    					+ " WHERE date(" + Time.DATE_START + ") >= date('" + formattedDate + "','start of month')"
    					+ " AND date(" + Time.DATE_START + ") < date('" + formattedDate + "','start of month','+1 " + timeFrame + "')"
    					+ " AND " + Qualified.TIME_PUBLISHER_ID + " = " + publisherID
    					+ " AND " + Qualified.ENTRY_TYPE_RBC + " <> 1"
    					+ " UNION "
    					+ " SELECT " + Time.DATE_START + "||\" \"||" + Time.TIME_START + UnionsNameAsCols.DATE_START + "," + Time.DATE_END + "||\" \"||" + Time.TIME_END + UnionsNameAsCols.DATE_END + "," + Time._ID
    					+ " FROM " + Tables.TIMES
						+ " WHERE date(" + Time.DATE_START + ") >= date('" + formattedDate + "','start of month')"
						+ " AND date(" + Time.DATE_START + ") < date('" + formattedDate + "','start of month','+1 " + timeFrame + "')"
						+ " AND " + Time.PUBLISHER_ID + " = " + publisherID
						+ " AND " + Time.ENTRY_TYPE_ID + " = 0";
    	
    	return sqlDB.rawQuery(sql1, null);
    }
	
	public Cursor fetchListOfHoursForPublisherNoRollover(String formattedDate, int publisherID, String timeFrame) {
		String sql1 =	"SELECT " + Time.DATE_START + "||\" \"||" + Time.TIME_START + UnionsNameAsCols.DATE_START + "," + Time.DATE_END + "||\" \"||" + Time.TIME_END + UnionsNameAsCols.DATE_END + "," + Qualified.TIME_ID
    					+ " FROM " + Tables.TIMES
    					+ " INNER JOIN " + Tables.ENTRY_TYPES + " ON " + Qualified.ENTRY_TYPE_ID + " = " + Qualified.TIME_ENTRY_TYPE_ID
    					+ " WHERE date(" + Time.DATE_START + ") >= date('" + formattedDate + "','start of month')"
    					+ " AND date(" + Time.DATE_START + ") < date('" + formattedDate + "','start of month','+1 " + timeFrame + "')"
    					+ " AND " + Qualified.TIME_PUBLISHER_ID + " = " + publisherID
    					+ " AND " + Qualified.ENTRY_TYPE_RBC + " <> 1"
    					+ " AND " + Qualified.TIME_ENTRY_TYPE_ID + " <> " + MinistryDatabase.ID_ROLLOVER
    					+ " UNION "
    					+ " SELECT " + Time.DATE_START + "||\" \"||" + Time.TIME_START + UnionsNameAsCols.DATE_START + "," + Time.DATE_END + "||\" \"||" + Time.TIME_END + UnionsNameAsCols.DATE_END + "," + Time._ID
    					+ " FROM " + Tables.TIMES
						+ " WHERE date(" + Time.DATE_START + ") >= date('" + formattedDate + "','start of month')"
						+ " AND date(" + Time.DATE_START + ") < date('" + formattedDate + "','start of month','+1 " + timeFrame + "')"
						+ " AND " + Time.PUBLISHER_ID + " = " + publisherID
						+ " AND " + Time.ENTRY_TYPE_ID + " = 0";
    	
    	return sqlDB.rawQuery(sql1, null);
    }
	
	public Cursor fetchListOfRBCHoursForPublisher(String formattedDate, int publisherID) {
		String sql1 =	"SELECT " + Time.DATE_START + "||\" \"||" + Time.TIME_START + UnionsNameAsCols.DATE_START + "," + Time.DATE_END + "||\" \"||" + Time.TIME_END + UnionsNameAsCols.DATE_END + "," + Qualified.TIME_ID
    					+ " FROM " + Tables.TIMES
    					+ " INNER JOIN " + Tables.ENTRY_TYPES + " ON " + Qualified.ENTRY_TYPE_ID + " = " + Qualified.TIME_ENTRY_TYPE_ID
    					+ " WHERE date(" + Time.DATE_START + ") >= date('" + formattedDate + "','start of month')"
    					+ " AND date(" + Time.DATE_START + ") < date('" + formattedDate + "','start of month','+1 month')"
    					+ " AND " + Qualified.TIME_PUBLISHER_ID + " = " + publisherID
    					+ " AND " + Qualified.ENTRY_TYPE_RBC + " = 1";
    	
    	return sqlDB.rawQuery(sql1, null);
    }
	
	public int fetchRecordCountOfRBCHoursForMonthForPublisher(String formattedDate, int publisherID) {
		String sql =	"SELECT COUNT(" + Qualified.TIME_ID + ")"
    					+ " FROM " + Tables.TIMES
    					+ Joins.ENTRY_TYPES_ON_TIME
    					+ " WHERE date(" + Qualified.TIME_DATE_START + ") >= date('" + formattedDate + "','start of month')"
    					+ " AND date(" + Qualified.TIME_DATE_START + ") < date('" + formattedDate + "','start of month','+1 month')"
    					+ " AND " + Qualified.TIME_PUBLISHER_ID + " = " + publisherID
    					+ " AND " + Qualified.ENTRY_TYPE_RBC + " = 1";
    	Cursor cursor = sqlDB.rawQuery(sql, null);
    	if(cursor.moveToFirst())
    		return cursor.getInt(0);
    	else
    		return 0;
    	
    }
	
	public Cursor fetchListOfHoursForMonthForPublisher(SQLiteDatabase db, String formattedDate, int publisherID) {
		String sql1 =	"SELECT " + Time.DATE_START + "||\" \"||" + Time.TIME_START + UnionsNameAsCols.DATE_START + "," + Time.DATE_END + "||\" \"||" + Time.TIME_END + UnionsNameAsCols.DATE_END + "," + Qualified.TIME_ID
    					+ " FROM " + Tables.TIMES
    					+ " INNER JOIN " + Tables.ENTRY_TYPES + " ON " + Qualified.ENTRY_TYPE_ID + " = " + Qualified.TIME_ENTRY_TYPE_ID
    					+ " WHERE date(" + Time.DATE_START + ") >= date('" + formattedDate + "','start of month')"
    					+ " AND date(" + Time.DATE_START + ") < date('" + formattedDate + "','start of month','+1 month')"
    					+ " AND " + Qualified.TIME_PUBLISHER_ID + " = " + publisherID
    					+ " AND " + Qualified.ENTRY_TYPE_RBC + " <> 1"
    					+ " UNION "
    					+ " SELECT " + Time.DATE_START + "||\" \"||" + Time.TIME_START + UnionsNameAsCols.DATE_START + "," + Time.DATE_END + "||\" \"||" + Time.TIME_END + UnionsNameAsCols.DATE_END + "," + Time._ID
    					+ " FROM " + Tables.TIMES
						+ " WHERE date(" + Time.DATE_START + ") >= date('" + formattedDate + "','start of month')"
						+ " AND date(" + Time.DATE_START + ") < date('" + formattedDate + "','start of month','+1 month')"
						+ " AND " + Time.PUBLISHER_ID + " = " + publisherID
						+ " AND " + Time.ENTRY_TYPE_ID + " = 0";
    	
    	return db.rawQuery(sql1, null);
    }
	
	public Cursor fetchHoursForYearForPublisher(String formattedDate, int publisherID) {
		String sql1 =	"SELECT " + Time.DATE_START + "||\" \"||" + Time.TIME_START + UnionsNameAsCols.DATE_START + "," + Time.DATE_END + "||\" \"||" + Time.TIME_END + UnionsNameAsCols.DATE_END + "," + Qualified.TIME_ID
    					+ " FROM " + Tables.TIMES
    					+ " INNER JOIN " + Tables.ENTRY_TYPES + " ON " + Qualified.ENTRY_TYPE_ID + " = " + Qualified.TIME_ENTRY_TYPE_ID
    					+ " WHERE date(" + Time.DATE_START + ") >= date('" + formattedDate + "','start of month')"
    					+ " AND date(" + Time.DATE_START + ") < date('" + formattedDate + "','start of month','+1 year')"
    					+ " AND " + Qualified.TIME_PUBLISHER_ID + " = " + publisherID
    					+ " AND " + Qualified.ENTRY_TYPE_RBC + " <> 1"
    					+ " UNION "
    					+ " SELECT " + Time.DATE_START + "||\" \"||" + Time.TIME_START + UnionsNameAsCols.DATE_START + "," + Time.DATE_END + "||\" \"||" + Time.TIME_END + UnionsNameAsCols.DATE_END + "," + Time._ID
    					+ " FROM " + Tables.TIMES
						+ " WHERE date(" + Time.DATE_START + ") >= date('" + formattedDate + "','start of month')"
						+ " AND date(" + Time.DATE_START + ") < date('" + formattedDate + "','start of month','+1 year')"
						+ " AND " + Time.PUBLISHER_ID + " = " + publisherID
						+ " AND " + Time.ENTRY_TYPE_ID + " = 0";
    	
    	return sqlDB.rawQuery(sql1, null);
    }
	
	public Cursor fetchPublisher(int _id) {
		return sqlDB.query(Tables.PUBLISHERS
				,new String[] {Publisher._ID,Publisher.NAME,Publisher.ACTIVE}
				,Publisher._ID + " = " + _id
				,null
				,null
				,null
				,null
				,"1");
	}
	
	public long createPublisher(ContentValues values) {
    	return sqlDB.insert(Tables.PUBLISHERS,null,values);
    }
	
	public long createPublication(ContentValues values) {
    	return sqlDB.insert(Tables.LITERATURE,null,values);
    }
    
	public Cursor fetchActiveHouseholders() {
		return sqlDB.query(Tables.HOUSEHOLDERS, new String[] {Householder._ID, Householder.NAME}, Householder.ACTIVE + " = 1", null, null, null, Householder.DEFAULT_SORT, null);
    }
    
	public Cursor fetchAllHouseholdersWithActivityDates() {
		String sql =	"SELECT " + Householder._ID + "," + Householder.NAME + UnionsNameAsCols.TITLE + "," + Householder.ACTIVE + UnionsNameAsCols.ACTIVE
						+ ", (SELECT " + Qualified.TIME_DATE_START + " FROM " + Tables.TIMES + Joins.TIMEHOUSEHOLDER_JOIN_TIME + " WHERE " + Qualified.TIMEHOUSEHOLDER_HOUSEHOLDER_ID + " = " + Qualified.HOUSEHOLDER_ID + " ORDER BY " + Qualified.TIME_DATE_START + " DESC LIMIT 1)" + UnionsNameAsCols.DATE
						+ "," + MinistryDatabase.ID_UNION_TYPE_PERSON + UnionsNameAsCols.TYPE_ID
						+ " FROM " + Tables.HOUSEHOLDERS
						+ " ORDER BY " + Householder.DEFAULT_SORT;
    	
    	return sqlDB.rawQuery(sql, null);
    }
	
	public Cursor fetchHouseholder(int _id) {
		return sqlDB.query(Tables.HOUSEHOLDERS
				,new String[] {Householder._ID,Householder.NAME,Householder.ADDR,Householder.MOBILE_PHONE,Householder.HOME_PHONE,Householder.WORK_PHONE,Householder.OTHER_PHONE,Householder.ACTIVE}
				,Householder._ID + " = " + _id
				,null
				,null
				,null
				,null
				,"1");
	}
	
	public long createHouseholder(ContentValues values) {
    	return sqlDB.insert(Tables.HOUSEHOLDERS,null,values);
    }
	
    public Cursor fetchTimeEntry(int _id) {
    	return sqlDB.query(Tables.TIMES
				,new String[] {Time._ID,Time.PUBLISHER_ID,Time.ENTRY_TYPE_ID,Time.DATE_START,Time.DATE_END,Time.TIME_START,Time.TIME_END}
				,Time._ID + " = " + _id
				,null
				,null
				,null
				,null
				,"1");
    }
    
    public Cursor fetchActiveEntryTypes() {
		return sqlDB.query(Tables.ENTRY_TYPES, new String[] {EntryType._ID,EntryType.NAME}, EntryType.ACTIVE + "=" + ACTIVE, null, null, null, EntryType.DEFAULT_SORT, null);
    }
    
    public Cursor fetchAllEntryTypes() {
		return sqlDB.query(Tables.ENTRY_TYPES, new String[] {EntryType._ID,EntryType.NAME,EntryType.ACTIVE}, null, null, null, null, EntryType.DEFAULT_SORT);
    }
    
    public Cursor fetchAllEntryTypes(String sort) {
		String sql =	"SELECT " + EntryType._ID + "," + EntryType.NAME + "," + EntryType.ACTIVE
						+ " FROM " + Tables.ENTRY_TYPES
						+ " ORDER BY " + EntryType.NAME + " " + sort;
		
		return sqlDB.rawQuery(sql, null);
    }
    
    public Cursor fetchAllEntryTypesByPopularity() {
		String sql =	"SELECT " + EntryType._ID + ", (SELECT COUNT(" + Qualified.TIME_ID + ") FROM " + Tables.TIMES + " WHERE " + Qualified.TIME_ENTRY_TYPE_ID + "=" + Qualified.ENTRY_TYPE_ID + ") AS thecount" 
						+ " FROM " + Tables.ENTRY_TYPES
						+ " ORDER BY thecount DESC";
		
		return sqlDB.rawQuery(sql, null);
    }
    
    public Cursor fetchAllPublicationTypes() {
		return sqlDB.query(Tables.TYPES_OF_LIERATURE, new String[] {LiteratureType._ID,LiteratureType.NAME,LiteratureType.ACTIVE}, null, null, null, null, LiteratureType.DEFAULT_SORT);
    }
    
    public Cursor fetchAllPublicationTypes(String sort) {
		String sql =	"SELECT " + LiteratureType._ID + "," + LiteratureType.NAME + "," + LiteratureType.ACTIVE
						+ " FROM " + Tables.TYPES_OF_LIERATURE
						+ " ORDER BY " + LiteratureType.NAME + " " + sort;
		
		return sqlDB.rawQuery(sql, null);
    }
    
    public Cursor fetchAllPublicationTypesByPopularity() {
		String sql =	"SELECT " + LiteratureType._ID + ", (SELECT COUNT(" + Qualified.PLACED_LITERATURE_ID + ") FROM " + Tables.PLACED_LITERATURE
															+ Joins.LITERATURE_JOIN_PLACED_LITERATURE
															+ " WHERE " + Qualified.LITERATURE_TYPE_ID_LINK + "=" + Qualified.TYPE_OF_LITERATURE_ID + ") AS thecount" 
						+ " FROM " + Tables.TYPES_OF_LIERATURE
						+ " ORDER BY thecount DESC";
		
		return sqlDB.rawQuery(sql, null);
    }
    
    public Cursor fetchAllEntryTypesButID(int id) {
		return sqlDB.query(Tables.ENTRY_TYPES, new String[] {EntryType._ID,EntryType.NAME}, EntryType._ID + " NOT IN (" + id + "," + MinistryDatabase.ID_ROLLOVER + ")", null, null, null, EntryType.DEFAULT_SORT);
    }
    
    public Cursor fetchEntryType(int _id) {
    	return sqlDB.query(Tables.ENTRY_TYPES
				,new String[] {EntryType._ID,EntryType.NAME,EntryType.ACTIVE,EntryType.RBC,EntryType.SORT_ORDER}
				,EntryType._ID + " = " + _id
				,null
				,null
				,null
				,null
				,"1");
    }
    
    public Cursor fetchActiveTypesOfLiterature() {
		return sqlDB.query(Tables.TYPES_OF_LIERATURE, new String[] {LiteratureType._ID,LiteratureType.NAME}, LiteratureType.ACTIVE + "=" + ACTIVE, null, null, null, LiteratureType.DEFAULT_SORT, null);
    }
    
    public Cursor fetchTypesOfLiteratureToManage() {
		String sql =	"SELECT " + LiteratureType._ID + "," + LiteratureType.NAME + "," + LiteratureType.ACTIVE + "," + LiteratureType.SORT_ORDER + "," + "1 as isDefault"
						+ " FROM " + Tables.TYPES_OF_LIERATURE
						+ " WHERE " + LiteratureType._ID + " <= " + MinistryDatabase.MAX_PUBLICATION_TYPE_ID
						
						+ " UNION "
						
						+ " SELECT " + LiteratureType._ID + "," + LiteratureType.NAME + "," + LiteratureType.ACTIVE + "," + LiteratureType.SORT_ORDER + "," + "0 as isDefault"
						+ " FROM " + Tables.TYPES_OF_LIERATURE
						+ " WHERE " + LiteratureType._ID + " > " + MinistryDatabase.MAX_PUBLICATION_TYPE_ID
						
						+ " ORDER BY " + LiteratureType.DEFAULT_SORT;
		
		return sqlDB.rawQuery(sql, null);
    }
    
    public Cursor fetchTypesOfLiteratureCountsForPublisher(int publisherID, String formattedDate, String timeFrame) {
    	String sql =  "SELECT " + Qualified.TYPE_OF_LITERATURE_ID + "," + Qualified.TYPE_OF_LITERATURE_NAME +	","
    				+ "(SELECT SUM(" + Qualified.PLACED_LITERATURE_COUNT + " * " + Qualified.LITERATURE_WEIGHT + ") AS " + LiteraturePlaced.COUNT
					+ " FROM " + Tables.PLACED_LITERATURE
					+ Joins.LITERATURE_JOIN_PLACED_LITERATURE
					+ " WHERE " + Qualified.LITERATURE_TYPE_ID_LINK + " = " + Qualified.TYPE_OF_LITERATURE_ID
					+ " AND " + Qualified.PLACED_LITERATURE_PUBLISHER_ID + " = " + publisherID
					+ " AND date(" + Qualified.PLACED_LITERATURE_DATE + ") >= date('" + formattedDate + "','start of month')"
					+ " AND date(" + Qualified.PLACED_LITERATURE_DATE + ") < date('" + formattedDate + "','start of month','+1 " + timeFrame + "'))"
					+ " FROM " + Tables.TYPES_OF_LIERATURE
					+ " WHERE " + LiteratureType.ACTIVE + " = " + ACTIVE
					+ " ORDER BY " + LiteratureType.DEFAULT_SORT;
    	
		return sqlDB.rawQuery(sql, null);
    }
    
    public Cursor fetchEntryTypeCountsForPublisher(int publisherID, String formattedDate, String timeFrame) {
    	String sql =  "SELECT " + EntryType._ID + "," + EntryType.NAME + "," + fetchStudyCountForPublisher(formattedDate, timeFrame, publisherID)
    				+ " FROM " + Tables.ENTRY_TYPES
    				+ " WHERE " + EntryType._ID + "=" + MinistryDatabase.ID_BIBLE_STUDY
    				+ " UNION "
    				+ " SELECT " + EntryType._ID + "," + EntryType.NAME + "," + fetchReturnVisitCountForPublisher(formattedDate, timeFrame, publisherID)
    				+ " FROM " + Tables.ENTRY_TYPES
					+ " WHERE " + EntryType._ID + "=" + MinistryDatabase.ID_RETURN_VISIT
					+ " UNION "
					+ " SELECT " + EntryType._ID + "," + EntryType.NAME + "," + fetchRecordCountOfRBCHoursForMonthForPublisher(formattedDate, publisherID)
					+ " FROM " + Tables.ENTRY_TYPES
					+ " WHERE " + EntryType._ID + "=" + MinistryDatabase.ID_RBC;
    	
		return sqlDB.rawQuery(sql, null);
    }
    
    public Cursor fetchTypeOfLiterature(int _id) {
    	return sqlDB.query(Tables.TYPES_OF_LIERATURE
				,new String[] {LiteratureType._ID,LiteratureType.NAME,LiteratureType.ACTIVE}
				,LiteratureType._ID + " = " + _id
				,null
				,null
				,null
				,null
				,"1");
    }
    
    public Cursor fetchLiteratureByType(int _typeID) {
    	String sql =	"SELECT " + Qualified.LITERATURE_ID + "," + Qualified.LITERATURE_NAME + "," + Qualified.LITERATURE_ACTIVE
						+ ", (SELECT " + Qualified.TIME_DATE_START + " FROM " + Tables.TIMES + Joins.PLACED_LITERATURE_ON_TIME + " WHERE " + Qualified.PLACED_LITERATURE_LIT_ID + " = " + Qualified.LITERATURE_ID + " ORDER BY " + Qualified.TIME_DATE_START + " DESC LIMIT 1) AS " + Time.DATE_START
						+ " FROM " + Tables.LITERATURE
						+ " WHERE " + Qualified.LITERATURE_TYPE_ID_LINK + " = " + _typeID
						+ " ORDER BY " + Literature.DEFAULT_SORT;

    	return sqlDB.rawQuery(sql, null);
    }
    
    public Cursor fetchAllPublications(String sort) {
    	String sql =	"SELECT " + Qualified.LITERATURE_ID + "," + Qualified.LITERATURE_NAME
						+ " FROM " + Tables.LITERATURE
						+ " ORDER BY " + Qualified.LITERATURE_NAME + " " + sort;

    	return sqlDB.rawQuery(sql, null);
    }
    
    public Cursor fetchPublicationsByPopularity() {
		String sql =	"SELECT " + Literature._ID + ", (SELECT SUM(" + Qualified.PLACED_LITERATURE_COUNT + "*" + Qualified.LITERATURE_WEIGHT + ") FROM " + Tables.PLACED_LITERATURE + " WHERE " + Qualified.PLACED_LITERATURE_LIT_ID + "=" + Qualified.LITERATURE_ID + ") AS thecount" 
						+ " FROM " + Tables.LITERATURE
						+ " ORDER BY thecount DESC";
		
		return sqlDB.rawQuery(sql, null);
    }
    
    public Cursor fetchLiteratureByTypeWithActivityDates(int _typeID) {
    	String sql =	"SELECT " + Qualified.LITERATURE_ID + "," + Qualified.LITERATURE_NAME + UnionsNameAsCols.TITLE + "," + Qualified.LITERATURE_ACTIVE + UnionsNameAsCols.ACTIVE
						+ ", (SELECT " + Qualified.TIME_DATE_START + " FROM " + Tables.TIMES + Joins.PLACED_LITERATURE_ON_TIME + " WHERE " + Qualified.PLACED_LITERATURE_LIT_ID + " = " + Qualified.LITERATURE_ID + " ORDER BY " + Qualified.TIME_DATE_START + " DESC LIMIT 1)" +  UnionsNameAsCols.DATE
						+ "," + MinistryDatabase.ID_UNION_TYPE_PERSON + UnionsNameAsCols.TYPE_ID
						+ " FROM " + Tables.LITERATURE
						+ " WHERE " + Qualified.LITERATURE_TYPE_ID_LINK + " = " + _typeID
						+ " ORDER BY " + Literature.DEFAULT_SORT;
    	
    	return sqlDB.rawQuery(sql, null);
    }
    
    public Cursor fetchLiteratureByID(int _id) {
    	return sqlDB.query(Tables.LITERATURE, new String[] {Literature._ID,Literature.NAME,Literature.TYPE_OF_LIERATURE_ID,Literature.ACTIVE,Literature.WEIGHT}, Literature._ID + " = " + _id, null, null, null, null, null);
    }
	
	public long createLiterature(ContentValues values) {
    	return sqlDB.insert(Tables.LITERATURE,null,values);
    }
	
    public Cursor fetchRolloverRecord(int publisherID, String date) {
    	return sqlDB.query(Tables.ROLLOVER, Rollover.All_COLS, Rollover.PUBLISHER_ID + " = " + publisherID + " AND " + Rollover.DATE + " = date('" + date + "','start of month')", null, null, null, null, "1");
    }
	
    public int fetchRolloverMinutes(int publisherID, String date) {
    	int retVal = 0;
    	Cursor record = sqlDB.query(Tables.ROLLOVER, new String[] {Rollover.MINUTES}, Rollover.PUBLISHER_ID + " = " + publisherID + " AND " + Rollover.DATE + " = date('" + date + "','start of month')", null, null, null, null, "1");
    	if(record.moveToFirst())
			retVal = record.getInt(0); 
    	if(record != null && !record.isClosed())
    		record.close();
		return retVal;
    }
	
	public long createRolloverMinutes(ContentValues values) {
    	return sqlDB.insert(Tables.ROLLOVER,null,values);
    }
	
	public int saveRolloverMinutes(int _id, ContentValues values) {
		return sqlDB.update(Tables.ROLLOVER, values, BaseColumns._ID + " = " + _id, null);
	}
	
    public Cursor fetchRolloverRecord(SQLiteDatabase db, int publisherID, String date) {
    	return db.query(Tables.ROLLOVER, Rollover.All_COLS, Rollover.PUBLISHER_ID + " = " + publisherID + " AND " + Rollover.DATE + " = date('" + date + "','start of month')", null, null, null, null, "1");
    }
	
	public long createRolloverMinutes(SQLiteDatabase db, ContentValues values) {
    	return db.insert(Tables.ROLLOVER,null,values);
    }
	
	public int saveRolloverMinutes(SQLiteDatabase db, int _id, ContentValues values) {
		return db.update(Tables.ROLLOVER, values, BaseColumns._ID + " = " + _id, null);
	}
	
	
	
	public Cursor fetchMostRecentRolloverDate(int publisherID) {
		return sqlDB.query(Tables.ROLLOVER, new String[] {Rollover._ID,Rollover.PUBLISHER_ID,Rollover.DATE,Rollover.MINUTES}, Rollover.PUBLISHER_ID + " = " + publisherID, null, null, null, "date(" + Rollover.DATE + ") DESC", "1");
	}
    
    public Cursor fetchRolloverTimeEntry(int publisherID, String formattedDate) {
    	return sqlDB.query(Tables.TIMES
				,new String[] {Time._ID,Time.PUBLISHER_ID,Time.ENTRY_TYPE_ID,Time.DATE_START,Time.DATE_END,Time.TIME_START,Time.TIME_START}
				,Time.PUBLISHER_ID + " = " + publisherID + " AND " + Time.ENTRY_TYPE_ID + " = " + MinistryDatabase.ID_ROLLOVER + " AND " + Time.DATE_START + " = '" + formattedDate + "'"
				,null
				,null
				,null
				,null
				,"1");
    }
	
	public void removeTimeEntry(int _id) {
		sqlDB.delete(Tables.TIMES, Time._ID + " = " + _id, null);
	}
	
	public void removeTimeEntryDeep(int timeID) {
		sqlDB.delete(Tables.NOTES, Notes.TIME_ID + " = " + timeID, null);
		sqlDB.delete(Tables.TIMES, Time._ID + " = " + timeID, null);
		sqlDB.delete(Tables.PLACED_LITERATURE, LiteraturePlaced.TIME_ID + " = " + timeID, null);
		sqlDB.delete(Tables.TIME_HOUSEHOLDERS, TimeHouseholder.TIME_ID + " = " + timeID, null);
	}
	
	public long createEntryType(ContentValues values) {
    	return sqlDB.insert(Tables.ENTRY_TYPES,null,values);
    }
	
	public long createLiteratureType(ContentValues values) {
    	return sqlDB.insert(Tables.TYPES_OF_LIERATURE,null,values);
    }
	
	public Cursor fetchTimeEntriesByPublisherAndMonth(int publisherID, String date, String timeFrame) {
		String sql = 	"SELECT " + Qualified.TIME_ID
						+ "," + Qualified.TIME_DATE_START
						+ "," + Qualified.TIME_DATE_END
						+ "," + Qualified.TIME_TIME_START
						+ "," + Qualified.TIME_TIME_END
						+ "," + Qualified.ENTRY_TYPE_NAME + UnionsNameAsCols.TITLE
						+ "," + Qualified.TIME_ENTRY_TYPE_ID
						+ " FROM " + Tables.TIMES
						+ " LEFT OUTER JOIN " + Tables.ENTRY_TYPES + " ON " + Qualified.ENTRY_TYPE_ID + " = " + Qualified.TIME_ENTRY_TYPE_ID
						+ " WHERE date(" + Time.DATE_START + ") >= date('" + date + "','start of month')"
    					+ " AND date(" + Time.DATE_START + ") < date('" + date + "','start of month','+1 " + timeFrame + "')"
    					+ " AND time." + Time.PUBLISHER_ID + " = " + publisherID
    					+ " ORDER BY date(" + Time.DATE_START + ") DESC, time(" + Time.TIME_END + ") DESC";
		
		return sqlDB.rawQuery(sql, null);
	}
	
	public Cursor fetchTimeEntriesByPublisher(int publisherId) {
		String sql = 	"SELECT " + Qualified.TIME_ID
						+ "," + Qualified.TIME_DATE_START
						+ "," + Qualified.TIME_DATE_END
						+ "," + Qualified.TIME_TIME_START
						+ "," + Qualified.TIME_TIME_END
						+ "," + Qualified.ENTRY_TYPE_NAME + UnionsNameAsCols.TITLE
						+ "," + Qualified.TIME_ENTRY_TYPE_ID
						+ " FROM " + Tables.TIMES
						+ " LEFT OUTER JOIN " + Tables.ENTRY_TYPES + " ON " + Qualified.ENTRY_TYPE_ID + " = " + Qualified.TIME_ENTRY_TYPE_ID
    					+ " ORDER BY date(" + Time.DATE_START + ") DESC, time(" + Time.TIME_END + ") DESC";
		
		return sqlDB.rawQuery(sql, null);
	}
	
	public Cursor fetchTimeEntriesByPublisherAndMonthNoRollover(int publisherID, String date, String timeFrame) {
		String sql = 	"SELECT " + Qualified.TIME_ID
						+ "," + Qualified.TIME_DATE_START
						+ "," + Qualified.TIME_DATE_END
						+ "," + Qualified.TIME_TIME_START
						+ "," + Qualified.TIME_TIME_END
						+ "," + Qualified.ENTRY_TYPE_NAME + UnionsNameAsCols.TITLE
						+ "," + Qualified.TIME_ENTRY_TYPE_ID
						+ " FROM " + Tables.TIMES
						+ " LEFT OUTER JOIN " + Tables.ENTRY_TYPES + " ON " + Qualified.ENTRY_TYPE_ID + " = " + Qualified.TIME_ENTRY_TYPE_ID
						+ " WHERE date(" + Time.DATE_START + ") >= date('" + date + "','start of month')"
    					+ " AND date(" + Time.DATE_START + ") < date('" + date + "','start of month','+1 " + timeFrame + "')"
    					+ " AND time." + Time.PUBLISHER_ID + " = " + publisherID
    					+ " AND time." + Time.ENTRY_TYPE_ID + " <> " + MinistryDatabase.ID_ROLLOVER
    					+ " ORDER BY date(" + Time.DATE_START + ") DESC, time(" + Time.TIME_END + ") DESC";
		
		return sqlDB.rawQuery(sql, null);
	}
	
	public long createPlacedLiterature(ContentValues values) {
    	return sqlDB.insert(Tables.PLACED_LITERATURE,null,values);
    }
    
    public void deleteTimeByID(int rowID) {
		sqlDB.delete(Tables.TIMES, "_id = " + rowID, null);
		sqlDB.delete(Tables.PLACED_LITERATURE, LiteraturePlaced.TIME_ID + "=" + rowID, null);
    }
    
    public boolean importDatabase(String dbPath, String packageName) throws IOException {	
    	/** Close the SQLiteOpenHelper so it will commit the created empty database to internal storage. */
    	close();
    	File newDb = new File(dbPath);
    	File oldDb = new File(Environment.getDataDirectory(), "/data/" + packageName + "/databases/" + MinistryDatabase.DATABASE_NAME);
    	
    	if (newDb.exists()) {
    		FileUtils.copyFile(newDb, oldDb);
    		/** Access the copied database so SQLiteHelper will cache it and mark it as created. */
    		sqlDB.close();
    		return true;
    	}
    	else
    		return false;
    }
    
    public boolean importDatabase(File newDB, File oldDB) throws IOException {	
    	/** Close the SQLiteOpenHelper so it will commit the created empty database to internal storage. */
    	close();
    	try {
    		FileUtils.copyFile(newDB, oldDB);
    		/** Access the copied database so SQLiteHelper will cache it and mark it as created. */
    		close();
    		return true;
    	} catch (Exception e) {
    		return false;
    	}	
    }
    
    public int saveLiterature(long _id, ContentValues values) {
    	return sqlDB.update(Tables.LITERATURE, values, BaseColumns._ID + "=" + _id, null);
    }
    
    public int saveHouseholder(long _id, ContentValues values) {
    	return sqlDB.update(Tables.HOUSEHOLDERS, values, BaseColumns._ID + "=" + _id, null);
    }
    
    public int savePublisher(long _id, ContentValues values) {
    	return sqlDB.update(Tables.PUBLISHERS, values, BaseColumns._ID + "=" + _id, null);
    }
    
    public int saveTimeHouseholder(long _id, ContentValues values) {
    	return sqlDB.update(Tables.TIME_HOUSEHOLDERS, values, BaseColumns._ID + "=" + _id, null);
    }
    
    public int saveTime(long _id, ContentValues values) {
    	return sqlDB.update(Tables.TIMES, values, BaseColumns._ID + "=" + _id, null);
    }
    
    public int savePlacedLiterature(long _id, ContentValues values) {
    	return sqlDB.update(Tables.PLACED_LITERATURE, values, BaseColumns._ID + "=" + _id, null);
    }
	
	public long createTimeHouseholder(ContentValues values) {
    	return sqlDB.insert(Tables.TIME_HOUSEHOLDERS,null,values);
    }
	
	public long createTime(ContentValues values) {
    	return sqlDB.insert(Tables.TIMES,null,values);
    }
    
    public int saveNotes(long _id, ContentValues values) {
    	return sqlDB.update(Tables.NOTES, values, BaseColumns._ID + "=" + _id, null);
    }
	
	public long createNotes(ContentValues values) {
    	return sqlDB.insert(Tables.NOTES,null,values);
    }
	
	public void deleteNoteByID(int _id) {
		sqlDB.delete(Tables.NOTES, Notes._ID + " = " + _id, null);
	}
    
    public int savePublicationType(long _id, ContentValues values) {
    	return sqlDB.update(Tables.TYPES_OF_LIERATURE, values, BaseColumns._ID + "=" + _id, null);
    }
    
    public int saveEntryType(long _id, ContentValues values) {
    	return sqlDB.update(Tables.ENTRY_TYPES, values, BaseColumns._ID + "=" + _id, null);
    }
	
	public void deleteNoteByTimeAndHouseholderID(int timeID, int householderID) {
		sqlDB.delete(Tables.NOTES, Notes.TIME_ID + " = " + timeID + " AND " + Notes.HOUSEHOLDER_ID + " = " + householderID, null);
	}
	
	public Cursor fetchTimeHouseholdersForTimeByID(int timeID) {
		String sql =	"SELECT " + Qualified.TIMEHOUSEHOLDER_ID + "," + Qualified.TIMEHOUSEHOLDER_HOUSEHOLDER_ID + "," + Qualified.HOUSEHOLDER_NAME + "," + TimeHouseholder.STUDY + "," + Qualified.NOTES_ID + UnionsNameAsCols.NOTE_ID + "," + Notes.NOTES + "," + Qualified.TIMEHOUSEHOLDER_IS_RETURN_VISIT
						+ " FROM " + Tables.TIMES
						+ " LEFT JOIN " + Tables.TIME_HOUSEHOLDERS + " ON (" + Qualified.TIMEHOUSEHOLDER_TIME_ID + " IN (0," + timeID + "))"
						+ LeftJoins.HOUSEHOLDERS_JOIN_TIMEHOUSEHOLDERS
						+ " LEFT JOIN " + Tables.NOTES + " ON (" + Qualified.NOTES_HOUSEHOLDER_ID + " = " + Qualified.TIMEHOUSEHOLDER_HOUSEHOLDER_ID + " AND " + Qualified.NOTES_TIME_ID + " = " + Qualified.TIME_ID + ")"
    					+ " WHERE " + Qualified.TIME_ID + " = " + timeID
    					+ " AND " + Qualified.TIME_ENTRY_TYPE_ID + " <> " + MinistryDatabase.ID_ROLLOVER;
		
    	return sqlDB.rawQuery(sql, null);
    }
	
	public Cursor fetchActivityForHouseholder(int householderID) {
		String sql = 	"SELECT " + Qualified.TIME_ID + "," + Qualified.TIME_DATE_START + "," + Qualified.NOTES_NOTES
							+ ", " + Qualified.PUBLISHER_NAME + UnionsNameAsCols.PUBLISHER_NAME
							+ ", " + Qualified.ENTRY_TYPE_NAME + UnionsNameAsCols.ENTRY_TYPE_NAME
							+ ", (SELECT COUNT(" + Qualified.PLACED_LITERATURE_ID + ") FROM " + Tables.PLACED_LITERATURE + " WHERE " + Qualified.PLACED_LITERATURE_TIME_ID + " = " + Qualified.TIME_ID + " AND " + Qualified.PLACED_LITERATURE_HOUSEHOLDER_ID + " = " + Qualified.TIMEHOUSEHOLDER_HOUSEHOLDER_ID + ")" + UnionsNameAsCols.COUNT
						+ " FROM " + Tables.TIME_HOUSEHOLDERS
						+ Joins.TIME_JOIN_TIMEHOUSEHOLDER
						+ Joins.ENTRY_TYPES_ON_TIME
						+ Joins.PUBLISHERS_ON_TIME
						+ LeftJoins.NOTES_ON_TIMEHOUSEHOLDER_AND_TIME
						+ " WHERE " + Qualified.TIMEHOUSEHOLDER_HOUSEHOLDER_ID + "=" + householderID
						+ " ORDER BY " + Qualified.TIME_DATE_START + " DESC, " + Qualified.TIME_TIME_START + " DESC, " + Qualified.ENTRY_TYPE_NAME;
		
		return sqlDB.rawQuery(sql, null);
	}
	
	public Cursor fetchActivityForPublisher(int publisherID) {
		String sql = 	"SELECT " + Qualified.TIME_ID
						+ "," + Qualified.TIME_DATE_START
						+ "," + Qualified.TIME_DATE_END
						+ "," + Qualified.TIME_TIME_START
						+ "," + Qualified.TIME_TIME_END
						+ "," + Qualified.ENTRY_TYPE_NAME + UnionsNameAsCols.TITLE
						+ " FROM " + Tables.TIMES
						+ Joins.ENTRY_TYPES_ON_TIME 
						+ " WHERE " + Qualified.TIME_PUBLISHER_ID + "=" + publisherID
						+ " AND " + Qualified.TIME_ENTRY_TYPE_ID + " <> " + MinistryDatabase.ID_ROLLOVER
						+ " ORDER BY " + Qualified.TIME_DATE_START + " DESC," + Qualified.TIME_TIME_START + " DESC";
		
		return sqlDB.rawQuery(sql, null);
	}
	
	public Cursor fetchActivityForLiterature(int literatureID) {
		String sql =	"SELECT "+ Qualified.TIME_ID + UnionsNameAsCols._ID
								+ ", " + Qualified.TIME_DATE_START + UnionsNameAsCols.DATE
								+ ", " + Qualified.PUBLISHER_NAME + UnionsNameAsCols.PUBLISHER_NAME
								+ ", " + Qualified.PLACED_LITERATURE_COUNT
								+ ", " + Qualified.HOUSEHOLDER_NAME + UnionsNameAsCols.HOUSEHOLDER_NAME
								+ ", " + Qualified.ENTRY_TYPE_NAME + UnionsNameAsCols.ENTRY_TYPE_NAME
						
						+ " FROM " + Tables.LITERATURE
						+ Joins.PLACED_LITERATURE_ON_LITERATURE_NAMES
						+ Joins.TIME_ON_PLACED_LITERATURE
						+ Joins.ENTRY_TYPES_ON_TIME
						+ Joins.PUBLISHERS_ON_PLACED_LITERATURE
						+ LeftJoins.HOUSEHOLDERS_JOIN_PLACED_LITERATURE
						+ " WHERE " + Qualified.LITERATURE_ID + " = " + literatureID
						+ " ORDER BY " + Qualified.TIME_DATE_START + " DESC, " + Qualified.ENTRY_TYPE_NAME + ", " + Qualified.HOUSEHOLDER_NAME;
		
		return sqlDB.rawQuery(sql, null);
	}
	
	public int fetchTimeHouseholderID(int timeID, int householderID) {
		int retVal = 0;
		String sql = 	"SELECT " + TimeHouseholder._ID
						+ " FROM " + Tables.TIME_HOUSEHOLDERS
						+ " WHERE " + TimeHouseholder.TIME_ID + "=" + timeID
						+ " AND " + TimeHouseholder.HOUSEHOLDER_ID + "=" + householderID;
		Cursor cursor = sqlDB.rawQuery(sql, null);
		if(cursor.moveToFirst())
			retVal = cursor.getInt(cursor.getColumnIndex(TimeHouseholder._ID));
		cursor.close();
		return retVal;
	}
	
	public void deleteTimeHouseholderOrphans(int timeID, long[] householderIDs) {
		StringBuilder builder = new StringBuilder();
		builder.append("0");
		for(long i : householderIDs) {
			builder.append(",");
			builder.append(i);
		}
		sqlDB.delete(Tables.TIME_HOUSEHOLDERS, TimeHouseholder.TIME_ID + "=" + timeID + " AND " + TimeHouseholder._ID + " NOT IN (" + builder.toString() + ")", null);
	}
	
	public void deleteTimeHouseholderLiteraturePlacedOrphans(int timeID, int[] householderIDs) {
		StringBuilder builder = new StringBuilder();
		builder.append(MinistryDatabase.CREATE_ID);
		for(int i : householderIDs) {
			builder.append(",");
			builder.append(i);
		}
		sqlDB.delete(Tables.PLACED_LITERATURE, LiteraturePlaced.TIME_ID + "=" + timeID + " AND " + LiteraturePlaced.HOUSEHOLDER_ID + " NOT IN (" + builder.toString() + ")", null);
	}
	
	public void deleteTimeHouseholderNotesOrphans(int timeID, int[] householderIDs) {
		StringBuilder builder = new StringBuilder();
		builder.append(MinistryDatabase.CREATE_ID);
		for(int i : householderIDs) {
			builder.append(",");
			builder.append(i);
		}
		sqlDB.delete(Tables.NOTES, Notes.TIME_ID + "=" + timeID + " AND " + Notes.HOUSEHOLDER_ID + " NOT IN (" + builder.toString() + ")", null);
	}
	
	public int fetchPlacedLitByTimeAndHouseholderAndLitID(int timeID, int householderID, int litID) {
		int retVal = 0;
		String sql = 	"SELECT " + LiteraturePlaced._ID
						+ " FROM " + Tables.PLACED_LITERATURE
						+ " WHERE " + LiteraturePlaced.TIME_ID + "=" + timeID
						+ " AND " + LiteraturePlaced.HOUSEHOLDER_ID + "=" + householderID
						+ " AND " + LiteraturePlaced.LITERATURE_ID + "=" + litID;
		Cursor cursor = sqlDB.rawQuery(sql, null);
		if(cursor.moveToFirst())
			retVal = cursor.getInt(cursor.getColumnIndex(LiteraturePlaced._ID));
		cursor.close();
		return retVal;
	}
	
	public Cursor fetchPlacedLitByTimeAndHouseholderID(int timeID, int householderID) {
		String sql = 	"SELECT " + Qualified.PLACED_LITERATURE_ID + "," + Qualified.PLACED_LITERATURE_LIT_ID + "," + Qualified.PLACED_LITERATURE_COUNT + "," + Qualified.LITERATURE_NAME + "," + Qualified.LITERATURE_TYPE_ID_LINK
						+ " FROM " + Tables.PLACED_LITERATURE
						+ Joins.LITERATURE_JOIN_PLACED_LITERATURE
						+ " WHERE " + LiteraturePlaced.TIME_ID + "=" + timeID
						+ " AND " + LiteraturePlaced.HOUSEHOLDER_ID + "=" + householderID
						+ " ORDER BY " + Literature.DEFAULT_SORT;
		return sqlDB.rawQuery(sql, null);
	}
	
	public void deletePlacedLiteratureOrphans(int timeID, int householderID, long[] placedIDs) {
		StringBuilder builder = new StringBuilder();
		builder.append("0");
		if(placedIDs != null) {
			for(long i : placedIDs) {
				builder.append(",");
				builder.append(i);
			}
		}
		String sql = 	"DELETE FROM " + Tables.PLACED_LITERATURE
						+ " WHERE " + LiteraturePlaced.TIME_ID + "=" + timeID
						+ " AND " + LiteraturePlaced.HOUSEHOLDER_ID + "=" + householderID
								+ " AND " + LiteraturePlaced._ID + " NOT IN (" + builder.toString() + ")";
		sqlDB.rawQuery(sql, null);
	}
	
	public void deleteLiteratureByID(int litID) {
		sqlDB.delete(Tables.PLACED_LITERATURE, LiteraturePlaced.LITERATURE_ID + " = " + litID, null);
		sqlDB.delete(Tables.LITERATURE, Literature._ID + " = " + litID, null);
	}
	
	public void deleteHouseholderByID(int _id) {
		sqlDB.delete(Tables.PLACED_LITERATURE, LiteraturePlaced.HOUSEHOLDER_ID + " = " + _id, null);
		sqlDB.delete(Tables.TIME_HOUSEHOLDERS, TimeHouseholder.HOUSEHOLDER_ID + " = " + _id, null);
		sqlDB.delete(Tables.HOUSEHOLDERS, Householder._ID + " = " + _id, null);
	}
	
	public void deletePublisherByID(int _id) {
		String sql = "DELETE FROM " + Tables.TIME_HOUSEHOLDERS + " WHERE " + TimeHouseholder.TIME_ID + " IN (SELECT " + Time._ID + " FROM " + Tables.TIMES + " WHERE " + Time.PUBLISHER_ID + " = " + _id + ")";
		sqlDB.rawQuery(sql, null);
		
		sqlDB.delete(Tables.TIMES, Time.PUBLISHER_ID + " = " + _id, null);
		sqlDB.delete(Tables.PLACED_LITERATURE, LiteraturePlaced.PUBLISHER_ID + " = " + _id, null);
		sqlDB.delete(Tables.ROLLOVER, Rollover.PUBLISHER_ID + " = " + _id, null);
		sqlDB.delete(Tables.PUBLISHERS, Publisher._ID + " = " + _id, null);
	}
	
	public Cursor fetchNotesByTimeAndHousehodlerID(int timeID, int householderID) {
		String sql =	"SELECT " + Notes._ID + "," + Notes.NOTES
						+ " FROM " + Tables.NOTES
    					+ " WHERE " + Notes.TIME_ID + " = " + timeID
    					+ " AND " + Notes.HOUSEHOLDER_ID + " = " + householderID;
    	return sqlDB.rawQuery(sql, null);
    }
	
	public Cursor fetchPublicationTypeByID(long _id) {
		String sql =	"SELECT " + LiteratureType._ID + "," + LiteratureType.NAME + "," + LiteratureType.ACTIVE
						+ " FROM " + Tables.TYPES_OF_LIERATURE
    					+ " WHERE " + LiteratureType._ID + " = " + _id;
    	return sqlDB.rawQuery(sql, null);
    }
	
	public void reassignPublications(int origID, int newID) {
		ContentValues values = new ContentValues();
		values.put(Literature.TYPE_OF_LIERATURE_ID, newID);
		sqlDB.update(Tables.LITERATURE, values, Literature.TYPE_OF_LIERATURE_ID + "=" + origID, null);
	}
	
	public void removePublication(int id) {
		sqlDB.delete(Tables.TYPES_OF_LIERATURE, LiteratureType._ID + " = " + id, null);
	}
	
	public void reassignEntryType(int origID, int newID) {
		ContentValues values = new ContentValues();
		values.put(Time.ENTRY_TYPE_ID, newID);
		sqlDB.update(Tables.TIMES, values, Time.ENTRY_TYPE_ID + "=" + origID, null);
	}
	
	public Cursor fetchDefaultPublicationTypes() {
		return sqlDB.query(Tables.TYPES_OF_LIERATURE, new String[] {LiteratureType._ID,LiteratureType.NAME}, LiteratureType._ID + "<=" + MinistryDatabase.MAX_PUBLICATION_TYPE_ID, null, null, null, LiteratureType.DEFAULT_SORT);
	}
	
	public void deleteEntryTypeByID(int _id) {
		sqlDB.delete(Tables.ENTRY_TYPES, EntryType._ID + " = " + _id, null);
	}
	
	public void processRolloverTime(int publisherID, Calendar requestedStartDate) {
		//SimpleDateFormat saveDateFormat	= new SimpleDateFormat("yyyy-MM-dd", Locale.getDefault());
		
		/** We'll start off by going back one month from the requested start date. This will ensure we always have accurate rollover minutes. */
		Calendar nextMonth = Calendar.getInstance(Locale.getDefault());
		Calendar start = Calendar.getInstance(Locale.getDefault());
		start.set(requestedStartDate.get(Calendar.YEAR), requestedStartDate.get(Calendar.MONTH), 1);
		start.add(Calendar.MONTH, -1);
		nextMonth.add(Calendar.MONTH, 1);
		
		ContentValues timeValues = new ContentValues();
    	timeValues.put(Time.PUBLISHER_ID, publisherID);
    	timeValues.put(Time.ENTRY_TYPE_ID, MinistryDatabase.ID_ROLLOVER);
    	timeValues.put(Time.TIME_START, "00:00"); // Midnight
    	
		ContentValues roValues = new ContentValues();
		roValues.put(Rollover.PUBLISHER_ID, publisherID);
		
		Cursor ro, time;
		int minutesRO = 0;
		int minutesTime = 0;
		int oneHour = 60;
		int roID = MinistryDatabase.CREATE_ID;
		boolean isFirstLoop = true;
		
		do {
			/** Let's get our minutes and rollover._id from the db for the publisher and date. */
			ro = fetchRolloverRecord(publisherID, TimeUtils.dbDateFormat.format(start.getTime()));
			if(ro.moveToFirst()) {
				roID = ro.getInt(ro.getColumnIndex(Rollover._ID));
				if(isFirstLoop) {
					minutesRO = ro.getInt(ro.getColumnIndex(Rollover.MINUTES));
					isFirstLoop = false;
				}
			}
			else {
				roID = MinistryDatabase.CREATE_ID;
			}
			
			ro.close();
			
			minutesTime = Integer.valueOf(Helper.getMinuteDuration(fetchListOfHoursForPublisherNoRollover(TimeUtils.dbDateFormat.format(start.getTime()), publisherID, "month")));
			
			/** Is there already a rollover time entry for this month? If so we need that time._id to update or delete. */
			time = fetchRolloverTimeEntry(publisherID, TimeUtils.dbDateFormat.format(start.getTime()));
			
			/** The sum of both minutes is over an hour */
			if(minutesTime + minutesRO >= oneHour) {
				/** The time entry should be for the needed minutes to put the total time to the next hour. */
				timeValues.put(Time.TIME_END, "00:" + String.valueOf(oneHour - minutesTime));
				
				minutesRO = minutesTime + minutesRO - oneHour;
    			
    			timeValues.put(Time.DATE_START, TimeUtils.dbDateFormat.format(start.getTime()));
            	timeValues.put(Time.DATE_END, TimeUtils.dbDateFormat.format(start.getTime()));
    			
    			if(time.moveToFirst())
    				saveTime(time.getLong(time.getColumnIndex(Time._ID)), timeValues);
    			else
    				createTime(timeValues);
			}
			else {
				minutesRO += minutesTime;
				if (time.moveToFirst()) {
					/** We have a time record that needs to be deleted since there won't be a time entry for this month. */
					removeTimeEntryDeep(time.getInt(time.getColumnIndex(Time._ID)));
				}
			}
			
			time.close();
			
			/** No matter what we'll make sure a rollover record exists for this publisher and date. */
			roValues.put(Rollover.DATE, TimeUtils.dbDateFormat.format(start.getTime()));
        	roValues.put(Rollover.MINUTES, minutesRO);
        	
        	if(roID != MinistryDatabase.CREATE_ID)
        		saveRolloverMinutes(roID, roValues);
        	else
        		createRolloverMinutes(roValues);
			
			start.add(Calendar.MONTH, 1);
        } while(start.before(nextMonth));
	}
	
	public long createPioneeringType(ContentValues values) {
    	return sqlDB.insert(Tables.TYPES_OF_PIONEERING, null, values);
    }
    
    public int savePioneeringType(long _id, ContentValues values) {
    	return sqlDB.update(Tables.TYPES_OF_PIONEERING, values, BaseColumns._ID + "=" + _id, null);
    }
	
	public void deletePioneeringType(long _id) {
		sqlDB.delete(Tables.TYPES_OF_PIONEERING, BaseColumns._ID + " = " + _id, null);
	}
	
	public Cursor fetchActivePioneeringTypes() {
		return sqlDB.query(Tables.TYPES_OF_PIONEERING, PioneeringType.All_COLS, null, null, null, null, PioneeringType.DEFAULT_SORT);
	}
	
	public Cursor fetchPublisherFirstTimeEntry(int publisherID) {
		return sqlDB.query(Tables.TIMES, new String[] {Time.DATE_START}, Time.PUBLISHER_ID + "=" + publisherID, null, null, null, Time.DATE_START, "1");
	}
}